package com.lnsf.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.lnsf.dao.IGoodsDao;
import com.lnsf.dbutils.DataAccess;
import com.lnsf.model.Goods;

public class GoodsDaoImpl implements IGoodsDao {

	@Override
	public List<Goods> select() {
		List<Goods> list = new ArrayList<Goods>();
		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rs = null;
		try {
			conn = DataAccess.getConnection();
			prep = conn.prepareStatement("select * from goods where mark = 1");
			rs = prep.executeQuery();
			while (rs.next()) {
				Goods goods = new Goods(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4),
						rs.getFloat(5), rs.getFloat(6), rs.getFloat(7),rs.getInt(8));
				list.add(goods);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DataAccess.closeConnection(rs, prep, conn);
		}
		return list;
	}
	
	@Override
	public List<Goods> selectAll() {
		List<Goods> list = new ArrayList<Goods>();
		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rs = null;
		try {
			conn = DataAccess.getConnection();
			prep = conn.prepareStatement("select * from goods where mark = 0");
			rs = prep.executeQuery();
			while (rs.next()) {
				Goods goods = new Goods(rs.getInt(1), rs.getInt(2), rs.getString(3), rs.getString(4),
						rs.getFloat(5), rs.getFloat(6), rs.getFloat(7),rs.getInt(8));
				list.add(goods);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DataAccess.closeConnection(rs, prep, conn);
		}
		return list;
	}

	@Override
	public int insert(Goods goods) {
		int flag = 0;
		Connection conn = null;
		PreparedStatement prep = null;
		try {
			conn = DataAccess.getConnection();
			prep = conn.prepareStatement("insert into goods values (null, ?, ?, ?,?,?,?,1)");
			prep.setInt(1, goods.getShId());
			prep.setString(2, goods.getgName());
			prep.setString(3, goods.getgDescription());
			prep.setFloat(4, goods.getSuppPrice());
			prep.setFloat(5, goods.getSalesPrice());
			prep.setFloat(6, goods.getDiscount());
			prep.execute();
			flag = 1;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DataAccess.closeConnection(prep, conn);
		}
		return flag;
	}

	@Override
	public int update(Goods goods) {
		int flag = 0;
		Connection conn = null;
		PreparedStatement prep = null;
		try {
			conn = DataAccess.getConnection();
			prep = conn.prepareStatement(
					"update goods set gName = ?, gDescription=?,suppPrice=?,salesPrice=?,discount =?,mark = 1  where gId = ? and shId=?");
			prep.setString(1, goods.getgName());
			prep.setString(2, goods.getgDescription());
			prep.setFloat(3, goods.getSuppPrice());
			prep.setFloat(4, goods.getSalesPrice());
			prep.setFloat(5, goods.getDiscount());
			prep.setInt(6, goods.getgId());
			prep.setInt(7, goods.getShId());
			prep.execute();
			flag = 1;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DataAccess.closeConnection(prep, conn);
		}
		return flag;
	}
	
	@Override
	public int updateByGnameAndShid(Goods goods) {
		int flag = 0;
		Connection conn = null;
		PreparedStatement prep = null;
		try {
			conn = DataAccess.getConnection();
			prep = conn.prepareStatement(
					"update goods set gDescription=?,suppPrice=?,salesPrice=?,discount =?,mark = 1  where gname = ? and shId=?");
			prep.setString(1, goods.getgDescription());
			prep.setFloat(2, goods.getSuppPrice());
			prep.setFloat(3, goods.getSalesPrice());
			prep.setFloat(4, goods.getDiscount());
			prep.setString(5, goods.getgName());
			prep.setInt(6, goods.getShId());
			prep.execute();
			flag = 1;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DataAccess.closeConnection(prep, conn);
		}
		return flag;
	}
	
	@Override
	public int delete(Goods goods) {
		int flag = 0;
		Connection conn = null;
		PreparedStatement prep = null;
		try {
			conn = DataAccess.getConnection();
			prep = conn.prepareStatement("update goods set mark = 0 where gId = ? and shId=?");
			prep.setInt(1, goods.getgId());
			prep.setInt(2, goods.getShId());
			prep.execute();
			flag = 1;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DataAccess.closeConnection(prep, conn);
		}
		return flag;
	}

	@Override
	public int getGid() {
		Connection conn = null;
		PreparedStatement prep = null;
		ResultSet rs = null;
		int gid = 0;
		try {
			conn = DataAccess.getConnection();
			prep = conn.prepareStatement("select max(gid) from goods");
			rs = prep.executeQuery();
			while (rs.next()) {
				gid = rs.getInt("max(gid)");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DataAccess.closeConnection(rs, prep, conn);
		}
		return gid;
	}

}
